package com.ce.framework.util;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.InputStreamReader;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.log4j.Logger;

import com.ce.framework.context.FrameworkContext;
import com.ce.framework.exception.AppException;
import com.ce.util.DbUtil;
import com.ce.util.OrgUtil;

/**
 * 嫦娥软件有限公司 版权所有 2013
 * 创建人： andy
 * 创建时间： 2011-1-6 上午10:48:07
 * 功能描述：用于系统数据库的更新类
 * ==================================
 * 修改历史
 * 修改人        修改时间      修改位置（函数名）
 *
 * ==================================
 */
public class DataBaseUpdateUtil {
	protected Logger log = Logger.getLogger(this.getClass());
	public final static String dir = "db";

	public static String indexFileName = "UPDATE_NEW.SQL";
	

	//以splitBy符号来分割语句，以文件名为最小执行单位。
	public final static String splitByTxt = "[^\\*]/\\s*\n";
	public final static String splitBySql = ";\\s*\n";

	private String localePath;
	private String publicPath;
	private static String contextPath;
	private static Connection updcon = null;
	public static PrintWriter outs =null;
	
	public int initSystem(String contextPath) throws AppException{
		indexFileName = "INDEX.SQL";
		int res = update(contextPath);
		indexFileName = "UPDATE_NEW.SQL";
		return res;
	}
	/**
	 * @author ： andy
	 * @since： Apr 28, 2012 11:22:05 AM
	 * 功能描述：数据库初始化及更新操作
	 * 方法的参数和返回值
	 * @return
	 * @throws AppException
	 * int 
	 * ==================================
	 * 修改历史
	 * 修改人        修改时间      修改原因及内容
	 *
	 * ==================================
	 */
	public int update(String contextPath) throws AppException{
		this.contextPath = contextPath;
		//获取数据库连接
		try {
			updcon = SystemContext.getConnection();
			updcon.setAutoCommit(true);
		} catch (Exception e) {			
			throw new AppException("数据库连接失败！");
		}
		String updfilename=null;
		List<String> updFileNameList=null;
		String[] updFileSqlArray=null;		
		if(1==createUpdateTable()){
			log.info("新创建了表SYS_T_DBUPD$NEWWAY");
		}
		publicPath=FrameworkContext.getAppPath()+"/"+dir+"/public/";
		updFileNameList=getUpdFileNameList(publicPath);
		if(null==updFileNameList||updFileNameList.isEmpty()){
			log.info("没有在目录["+publicPath + indexFileName+"]下发现数据库更新文件");
		}else{
			//执行文件中的SQL语句
			for(int i=0;i<updFileNameList.size();i++){
				updfilename = updFileNameList.get(i).toString();
				log.info("正在执行【"+publicPath+"】的数据库更新文件：" + updfilename);
				updFileSqlArray = readFile(updfilename);
				executeSqls(updFileSqlArray);
				write2database(updfilename);
			}		
		}
		/*
		 * 此处为1.0中升级各电务段数据的代码，该系统暂时不用	
			localePath=judgeLocalePath();
			if(null==localePath){
				log.info("找不到本服务器的数据库升级路径，请配置！");
			}
			else{
				updFileNameList=getUpdFileNameList(localePath);
				if(null==updFileNameList){
					log.info("不用升级本段的数据库");
					return -2;
				}
				else{
					//文件中的SQL语句
					for(int i=0;i<updFileNameList.size();i++){
						updfilename = updFileNameList.get(i).toString();
						log.info("正在执行数据库更新文件：" + updfilename);
						updFileSqlArray = readFile(updfilename);
						executeSqls(updFileSqlArray);
						write2database(updfilename);
					}
				}
			}
		*/	
		//关闭数据库连接
		try {
			updcon.close();
		} catch (SQLException e) {
			throw new AppException("系统初始化失败！");
		}
		//关闭日志文件
		try {
			if(outs!=null)
				outs.close();
		} catch (Exception e) {

			e.printStackTrace();
		}
		return 1;
	}
	private int write2database(String updfilename){
		String shortFileName=updfilename.replace(FrameworkContext.getAppPath(), "");//相对路径
		String sql="insert into SYS_T_DBUPD$NEWWAY values(sys_guid(),sysdate,'"+MD5.toMd5String(contextPath+shortFileName)+"','"+shortFileName+"','"+FrameworkContext.getAppPath()+"')";
		DbUtil.executeSqlUpdate(sql);
		return 1;
	}

	//创建一个表，用来存贮数据库更新日志，返回0表示表已存在，返回1表示新创建表
	private int createUpdateTable() {
		String sql = "select count(*) from user_tables t where t.table_name='SYS_T_DBUPD$NEWWAY'";
		if(DbUtil.getFirstIntSqlQuery(sql)==0){
			sql = "create table SYS_T_DBUPD$NEWWAY(vc_id varchar2(50) not null,dt_date date,vc_md5 varchar2(100),vc_updfilename  varchar2(4000),vc_app_path varchar2(4000))";
			DbUtil.executeSqlUpdate(sql);
			return 1;
		}
		return 0;
	}
	
	//是否需要执行此sql文件，执行过就不需要，未执行过就需要
	private Boolean need2beExecute(String sqlfilename){
		String sql = "select count(*) from SYS_T_DBUPD$NEWWAY where vc_md5='"+MD5.toMd5String(contextPath+sqlfilename)+"'";
		return 0==DbUtil.getFirstIntSqlQuery(sql);
	}
	
	//得到待更新文件列表，返回文件数目
	private List<String> getUpdFileNameList(String dirPath){
		int filecnt=0;
		List<String> updFileNameList = new ArrayList<String>();
		String fileNameListFile = dirPath + indexFileName;
		BufferedReader in = null;
		String line = null;
		String tmpFileName=null;
		File f = new File(fileNameListFile);
		if(!f.exists()){
			LogUtil.logInfo("file ["+f.getAbsolutePath()+"] not exist!");
			return updFileNameList;
		}
		try {
			in = new BufferedReader(new InputStreamReader(new FileInputStream(f), "UTF-8")); 
			line = in.readLine();
			while(line!=null){
				if(line.startsWith("@")){
					//去掉说明性的符号 
					tmpFileName = line.substring(1, line.length());
					tmpFileName = tmpFileName.trim();
					if(tmpFileName.endsWith(";")){
						tmpFileName=tmpFileName.substring(0, tmpFileName.length()-1);
					}
					//判断是否需要执行（未执行过的就需要执行)
					if(need2beExecute((dirPath+tmpFileName).replace(FrameworkContext.getAppPath(), ""))){
						updFileNameList.add(dirPath+tmpFileName);
						filecnt++;
					}
				}
				else if(line.startsWith("--")){
					//注释部分
					log.info(line.substring(1, line.length()));
				}
				line = in.readLine();
			}
			in.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

		return updFileNameList;
	}
	//找到当前服务器对应的升级目录，返回1表示找到，返回0表示未找到
	private String judgeLocalePath(){
		String localePath=null;
		String base = FrameworkContext.getAppPath();
		if(!base.endsWith("/") && !base.endsWith("\\"))base += "/";
		//publicPath = base + dir+"/"+publicDir+"/";
		String orgId = OrgUtil.getRootOrgId();
		File f = new File(base+dir);
		File[] fs = f.listFiles();
		if(fs==null)
			return null;
		for(int i=0;i<fs.length;i++){
			if(fs[i].getAbsolutePath().indexOf(orgId)>=0){//已找到
				localePath = fs[i].getAbsolutePath();
				if(!localePath.endsWith("/") && !localePath.endsWith("\\")){
					localePath += "/";
				}
				return localePath;
			}
		}
		return null;
	}
	//读取updfilename文件中的SQL语句，将语句返回
	private String[] readFile(String updfilename){
		String fullFileName = updfilename;
		BufferedReader in = null;
		String line = null;
		StringBuffer tmp = new StringBuffer();
		File f = new File(fullFileName);
		String fileExtName="TXT";
		if(updfilename.toUpperCase().endsWith(".SQL"))
		{
			fileExtName="SQL";
		}
		try {
			in = new BufferedReader(new InputStreamReader(new FileInputStream(f), "UTF-8")); 
			line = in.readLine();
			while(line!=null){
				if(line.startsWith("--")){
					log.info(line);
					line = in.readLine();
					continue;
				}
				if(line.startsWith("prompt")){
					log.info(line);
					line = in.readLine();
					continue;
				}
				tmp.append(line).append("\n");
				line = in.readLine();
			}
			in.close();
			tmp.append(line==null?"":line).append("\n");//最后加一个回车是以防文件最后只有/或;而没有回车
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		String filecontext = tmp.toString();
		//当为过程及函数时，不按照语句逐条分解
		if(updfilename.indexOf("others")>0)
			return new String[]{filecontext};
		//当为建表及初始化等语句时，按照语句逐条分解
		if("TXT".equals(fileExtName)){
			return filecontext.split(splitByTxt);		
		}
		else{
			return filecontext.split(splitBySql);	
		}
	}
	//执行多条SQL语句
	private int executeSqls(String[] updFileSqlArray) {
		int i=0;
		String sql=null;
		for(;i<updFileSqlArray.length;i++){
			sql = updFileSqlArray[i];
			sql = sql.trim();
			if(sql!=null &&!"null".equals(sql)&& sql.length()>0){
				Statement st = null;
				try {
					st = updcon.createStatement();
					st.executeUpdate(sql);
					st.close();
					logSql2File(sql);
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					log.error("以下语句执行失败（已记入日志文件"+localePath+"dbupdlog.txt）:\r\n"+sql);
					e.printStackTrace();
					logError2File(e);
				}

				//CodeTable.executeSqlUpdate(updFileSqlArray[i]);
			}
		}
		return i;
	}
	private void logSql2File(String sql) {
		PrintWriter out = getFilePrintStream();
		out.println(sql);
	}
	private void logError2File(SQLException e) {
		PrintWriter out = getFilePrintStream();
		e.printStackTrace(out);
	}
	private PrintWriter getFilePrintStream() {
		if(outs==null){
		//升级公共数据库
			try{
				outs = new PrintWriter(new FileWriter(publicPath+"dbupdlog.txt",true));
				outs.println("======================="+DateUtil.format(new Date(),"yyyy-MM-dd HH:mm:ss")+"==========================");
			}catch(Exception e){
				e.printStackTrace();
			}		
		}
		return outs;
	}
}
